#!/usr/bin/env python
# -*- encoding: utf-8 -*-

from sqlalchemy import create_engine
from sqlalchemy.types import VARCHAR,INTEGER,DECIMAL
import pandas as pd
import datetime
from sqlalchemy.pool import NullPool
from common.df_operator import *
from common import db_utils as util
import sys
from common import df_operator as op


def __step_0(start_time, end_time):
    sql = " DELETE FROM " \
          " BGTARAS1.T_ADS_FACT_SICB_UNINFO" \
          " WHERE 1=1 " \
          " AND REC_CREATE_TIME >= '%s'" \
          " AND REC_CREATE_TIME < '%s'" % (start_time, end_time)
    conn_rds.execute(sql)


def __step_1(start_time, end_time):
    sql = " SELECT " \
          " A.REC_CREATOR," \
          " A.REC_CREATE_TIME," \
          " A.REC_CREATOR as REC_REVISOR," \
          " A.REC_CREATE_TIME as REC_REVISE_TIME," \
          " A.UNIT_CODE," \
          " A.OUT_MAT_NO, " \
          " A.SHIFT_NO, " \
          " A.SHIFT_GROUP, " \
          " A.PROD_START_TIME, " \
          " A.PROD_END_TIME, " \
          " A.PROD_DATE, " \
          " A.IN_MAT_NO_1, " \
          " B.IN_MAT_THICK, " \
          " B.IN_MAT_WIDTH*10 as IN_MAT_WIDTH," \
          " B.IN_MAT_WT, " \
          " B.MAT_ACT_THICK," \
          " B.MAT_ACT_WIDTH, " \
          " A.OUT_MAT_ACT_LEN as MAT_ACT_LEN," \
          " B.MAT_ACT_WT," \
          " B.SG_SIGN," \
          " B.ST_NO," \
          " B.ANNEAL_DIAGRAM_CODE," \
          " B.COAT_CODE," \
          " A.TOP_COAT_WT_MGO," \
          " A.BOT_COAT_WT_MGO," \
          " B.TOP_COAT_THICK_AVG," \
          " B.BOT_COAT_THICK_AVG," \
          " A.SCRAP_WT_HEAD_OUT," \
          " A.SCRAP_WT_TAIL_OUT," \
          " A.TRIM_SCRAP_WIDTH_DS," \
          " A.TRIM_SCRAP_WIDTH_WS," \
          " A.DUMMY_COIL_FLAG" \
          " FROM MMSIJ4.TMMSIJ402 A" \
          " LEFT JOIN  TMSIJ4.TTMSIJ492 B" \
          " ON A.UNIT_CODE=B.UNIT_CODE AND A.OUT_MAT_NO=B.COIL_NO AND LEFT(A.PROD_END_TIME,10)=LEFT(B.END_PROD_TIME,10)  " \
          " WHERE" \
          " A.UNIT_CODE IN ('Q602','Q512','Q503','Q414','Q502','Q402','Q314','Q412','Q112','Q212','Q312','Q114','Q214','Q418','Q518','Q102','Q202','Q302','Q103','Q203','Q303')" \
          " AND A.REC_CREATE_TIME >=  '%s'  " \
          " AND A.REC_CREATE_TIME< '%s'" % (start_time, end_time)

    print(sql)

    df = util.query(conn_db2, sql)
    success = df.empty is False
    if success is False:
        return
    df1 = df
    df1.columns = df1.columns.str.upper()

    df1.rename(columns={'REC_CREATOR': 'REC_CREATOR'}, inplace=True)
    df1.rename(columns={'REC_CREATE_TIME': 'REC_CREATE_TIME'}, inplace=True)
    df1.rename(columns={'REC_REVISOR': 'REC_REVISOR'}, inplace=True)
    df1.rename(columns={'REC_REVISE_TIME': 'REC_REVISE_TIME'}, inplace=True)
    df1.rename(columns={'UNIT_CODE': 'UNIT_CODE'}, inplace=True)
    df1.rename(columns={'OUT_MAT_NO': 'PROD_COILNO'}, inplace=True)
    df1.rename(columns={'SHIFT_NO': 'SHIFT'}, inplace=True)
    df1.rename(columns={'SHIFT_GROUP': 'CLASS'}, inplace=True)
    df1.rename(columns={'PROD_START_TIME': 'PRODUCE_START_TIME'}, inplace=True)
    df1.rename(columns={'PROD_END_TIME': 'PRODUCE_END_TIME'}, inplace=True)
    df1.rename(columns={'PROD_DATE': 'PRODUCE_DATE'}, inplace=True)
    df1.rename(columns={'IN_MAT_NO_1': 'ENTRY_COILNO'}, inplace=True)
    df1.rename(columns={'IN_MAT_THICK': 'ENTRY_MAT_THICK'}, inplace=True)
    df1.rename(columns={'IN_MAT_WIDTH': 'ENTRY_MAT_WIDTH'}, inplace=True)
    df1.rename(columns={'IN_MAT_WT': 'ENTRY_MAT_WT'}, inplace=True)
    df1.rename(columns={'MAT_ACT_THICK': 'MAT_ACT_THICK'}, inplace=True)
    df1.rename(columns={'MAT_ACT_WIDTH': 'MAT_ACT_WIDTH'}, inplace=True)
    df1.rename(columns={'MAT_ACT_LEN': 'MAT_ACT_LEN'}, inplace=True)
    df1.rename(columns={'MAT_ACT_WT': 'ACT_OUTPUT_WT'}, inplace=True)
    df1.rename(columns={'SG_SIGN': 'SG_SIGN'}, inplace=True)
    df1.rename(columns={'ST_NO': 'STEELNO'}, inplace=True)
    df1.rename(columns={'ANNEAL_DIAGRAM_CODE': 'ANNEAL_CURVE'}, inplace=True)
    df1.rename(columns={'COAT_CODE': 'COATING_TYPE'}, inplace=True)
    df1.rename(columns={'TOP_COAT_WT_MGO': 'TOP_COATING_WT'}, inplace=True)
    df1.rename(columns={'BOT_COAT_WT_MGO': 'BOT_COATING_WT'}, inplace=True)
    df1.rename(columns={'TOP_COAT_THICK_AVG': 'TOP_COATING_THICK_AVG'}, inplace=True)
    df1.rename(columns={'BOT_COAT_THICK_AVG': 'BOT_COATING_THICK_AVG'}, inplace=True)
    df1.rename(columns={'SCRAP_WT_HEAD_OUT': 'EXIT_HEADCUT_SCRAP_WT'}, inplace=True)
    df1.rename(columns={'SCRAP_WT_TAIL_OUT': 'EXIT_TAILCUT_SCRAP_WT'}, inplace=True)
    df1.rename(columns={'TRIM_SCRAP_WIDTH_DS': 'DSIDE_TRIMMING_SCRAP_WID'}, inplace=True)
    df1.rename(columns={'TRIM_SCRAP_WIDTH_WS': 'OSIDE_TRIMMING_SCRAP_WID'}, inplace=True)
    df1.rename(columns={'DUMMY_COIL_FLAG': 'DMY_FLAG'}, inplace=True)
    df1.rename(columns={'PRE_UNIT_CODE': 'SOURCE_UNIT'}, inplace=True)
    df1.rename(columns={'IN_MAT_LEN': 'ENTRY_MAT_LEN'}, inplace=True)

    dtypedict = op.get_dict_type(df1)
    util.insert(df, conn_rds, "T_ADS_FACT_SICB_UNINFO", "BGTARAS1", dtypedict)

def __step_2(start_time,end_time):
    #Q418机组长度
    sql1 = """
        MERGE INTO (SELECT * FROM BGTARAS1.T_ADS_FACT_SICB_UNINFO WHERE UNIT_CODE='Q418') A
            USING (SELECT ENT_COILNO,ENT_LENG,ROW_NUMBER() OVER(PARTITION BY ENT_COILNO ORDER BY ENTRY_TIME DESC) AS RN 
            FROM BGTAROQ418.T_ODS_DTG_ECDT ) B
            ON  RIGHT(A.ENTRY_COILNO,9)=B.ENT_COILNO AND B.RN=1
            WHEN MATCHED THEN
            UPDATE SET A.ENTRY_MAT_LEN=B.ENT_LENG
            ELSE IGNORE
    """
    conn_rds.execute(sql1)

    sql2 = """
            MERGE INTO (SELECT * FROM BGTARAS1.T_ADS_FACT_SICB_UNINFO WHERE UNIT_CODE='Q518') A
            USING (SELECT ENT_COILNO,ENT_LENG,ROW_NUMBER() OVER(PARTITION BY ENT_COILNO ORDER BY ENTRY_TIME DESC) AS RN 
            FROM BGTAROQ518.DTG_ECDT ) B
            ON  A.ENTRY_COILNO=B.ENT_COILNO AND B.RN=1
            WHEN MATCHED THEN
            UPDATE SET A.ENTRY_MAT_LEN=B.ENT_LENG
            ELSE IGNORE
        """
    conn_rds.execute(sql2)

    sql3 = """
            MERGE INTO (SELECT * FROM BGTARAS1.T_ADS_FACT_SICB_UNINFO WHERE UNIT_CODE='Q412') A
            USING (SELECT ENT_COILNO,ENT_LENG,ROW_NUMBER() OVER(PARTITION BY ENT_COILNO ORDER BY ENTRY_TIME DESC) AS RN 
            FROM BGTAROQ412.DTG_ECDT ) B
            ON  RIGHT(A.ENTRY_COILNO,9)=B.ENT_COILNO AND B.RN=1
            WHEN MATCHED THEN
            UPDATE SET A.ENTRY_MAT_LEN=B.ENT_LENG
            ELSE IGNORE
    """
    conn_rds.execute(sql3)

    sql4 = """
            MERGE INTO (SELECT * FROM BGTARAS1.T_ADS_FACT_SICB_UNINFO WHERE UNIT_CODE='Q312') A
                USING (SELECT ENT_COILNO,ENT_LENG,ROW_NUMBER() OVER(PARTITION BY ENT_COILNO ORDER BY ENTRY_TIME DESC) AS RN 
                FROM BGTAROQ312.T_ODS_DTG_ECDT ) B
                ON  RIGHT(A.ENTRY_COILNO,9)=B.ENT_COILNO AND B.RN=1
                WHEN MATCHED THEN
                UPDATE SET A.ENTRY_MAT_LEN=B.ENT_LENG
                ELSE IGNORE
        """
    conn_rds.execute(sql4)

    sql5 = """
            MERGE INTO (SELECT * FROM BGTARAS1.T_ADS_FACT_SICB_UNINFO WHERE UNIT_CODE='Q214') A
            USING (SELECT ENT_COILNO,ENT_LENG,ROW_NUMBER() OVER(PARTITION BY ENT_COILNO ORDER BY ENTRY_TIME DESC) AS RN 
            FROM BGTAROQ214.T_ODS_DTG_ECDT ) B
            ON  RIGHT(A.ENTRY_COILNO,9)=B.ENT_COILNO AND B.RN=1
            WHEN MATCHED THEN
            UPDATE SET A.ENTRY_MAT_LEN=B.ENT_LENG
            ELSE IGNORE
        """
    conn_rds.execute(sql5)

    sql6 = """
            MERGE INTO (SELECT * FROM BGTARAS1.T_ADS_FACT_SICB_UNINFO WHERE UNIT_CODE='Q212') A
            USING (SELECT ENT_COILNO,ENT_LENG,ROW_NUMBER() OVER(PARTITION BY ENT_COILNO ORDER BY ENTRY_TIME DESC) AS RN 
            FROM BGTAROQ212.T_ODS_DTG_ECDT ) B
            ON  RIGHT(A.ENTRY_COILNO,9)=B.ENT_COILNO AND B.RN=1
            WHEN MATCHED THEN
            UPDATE SET A.ENTRY_MAT_LEN=B.ENT_LENG
            ELSE IGNORE
        """
    conn_rds.execute(sql6)

    sql7 = """
            MERGE INTO (SELECT * FROM BGTARAS1.T_ADS_FACT_SICB_UNINFO WHERE UNIT_CODE='Q114') A
            USING (SELECT ENT_COILNO,ENT_LENG,ROW_NUMBER() OVER(PARTITION BY ENT_COILNO ORDER BY ENTRY_TIME DESC) AS RN 
            FROM BGTAROQ114.T_ODS_DTG_ECDT ) B
            ON  RIGHT(A.ENTRY_COILNO,9)=B.ENT_COILNO AND B.RN=1
            WHEN MATCHED THEN
            UPDATE SET A.ENTRY_MAT_LEN=B.ENT_LENG
            ELSE IGNORE
        """
    conn_rds.execute(sql7)

    sql8 = """
            MERGE INTO (SELECT * FROM BGTARAS1.T_ADS_FACT_SICB_UNINFO WHERE UNIT_CODE='Q112') A
            USING (SELECT ENT_COILNO,ENT_LENG,ROW_NUMBER() OVER(PARTITION BY ENT_COILNO ORDER BY ENTRY_TIME DESC) AS RN 
            FROM BGTAROQ112.T_ODS_DTG_ECDT ) B
            ON  RIGHT(A.ENTRY_COILNO,9)=B.ENT_COILNO AND B.RN=1
            WHEN MATCHED THEN
            UPDATE SET A.ENTRY_MAT_LEN=B.ENT_LENG
            ELSE IGNORE
        """
    conn_rds.execute(sql8)

def UNINFO(start_time,end_time):
    global conn_db2, conn_rds
    conn_db2 = util.getConnectionDb2('10.70.16.170', '50170 ', 'dbprode7', 'dbprode7', 'dbprode7')
    conn_rds = util.getConnectionDb2('10.70.48.121', '50000 ', 'RG0PROD', 'bgtaroq000', 'bgtaroq000bgta')
    __step_0(start_time, end_time)
    __step_1(start_time, end_time)
    __step_2(start_time, end_time)
    conn_rds.close()
    conn_db2.close()

if __name__ == '__main__':
    UNINFO('20211123200000', '20211124200000')

